/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author zz
 */
public class DataProcessJdbcDao extends JdbcDaoSupport implements DataProcessDao {

    public List getFunction() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.query("select name from t_bi_function_list where bj=1",
                new RowMapperResultReader(new getFunctionRM()));

    }

    @SuppressWarnings("empty-statement")
    public void TsnAmt(Date adTsnDate, int aiShopId, String asShopBm,
            int aiDateNo, String asYear, int aiYybh, int aiMan_id,String asTname) {
        //开始处理
        int TsnAmt;
        int iRc;
        int aiYear;
        int TsnSort;
        int iWeekAmt=0,iYearAmt=0,iWeekAmt2=0,iYearAmt2=0;
        String sql="";
        float fWeekAddup=0,fYearAddup=0,fWeekAddup2=0,fYearAddup2=0;
        List lstWeek,lstYear;
        Date ldYesterday;
        String ls_mc="";
        System.out.println("now processing Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        JdbcTemplate jt = getJdbcTemplate();
        Object[] params = null;
        int[] types = null;

        if(asShopBm.equals("all")){
            sql = "select round(nvl(sum(je),0),1) year_addup from " + asTname +
                        " where saledate = ? ";
            params = new Object[]{adTsnDate};
            types = new int[1];
            types[0]  = Types.DATE;}
        else if(asShopBm.equals("yyb1")){
            sql = "select round(nvl(sum(je),0),1) year_addup from " + asTname +
                        " where saledate = ? and bm in (select bm from t_archi_dept where yybh=1)";
            params = new Object[]{adTsnDate};
            types = new int[1];
            types[0]  = Types.DATE;
        }else if(asShopBm.equals("yyb2")){
            sql = "select round(nvl(sum(je),0),1) year_addup from " + asTname +
                        " where saledate = ? and bm in (select bm from t_archi_dept where yybh=2)";
            params = new Object[]{adTsnDate};
            types = new int[1];
            types[0]  = Types.DATE;
        }else if(asShopBm.equals("yyb3")){
            sql = "select round(nvl(sum(je),0),1) year_addup from " + asTname +
                        " where saledate = ? and bm in (select bm from t_archi_dept where yybh=3)";
            params = new Object[]{adTsnDate};
            types = new int[1];
            types[0]  = Types.DATE;

        }else if(asShopBm.equals("23")){
            sql = "select round(nvl(sum(je),0),1) year_addup from " + asTname +
                        " where saledate = ? and ( bm='23' or bm='23a'  )";
            params = new Object[]{adTsnDate};
            types = new int[1];
            types[0]  = Types.DATE;

        }else{
            sql = "select round(nvl(sum(je),0),1) year_addup from " + asTname +
                        " where saledate = ? and bm=? ";
            params = new Object[]{adTsnDate, asShopBm};
            types = new int[2];
            types[0] = Types.DATE;
            types[1] = Types.VARCHAR;
        }
        lstWeek = jt.query(sql, params, types,new RowMapperResultReader(new TsnAmtMxYearRM()));
        float lf_tmp = 0;
        Iterator Iter = lstWeek.iterator();
        TsnAmtMx ltam = new TsnAmtMx();
        while (Iter.hasNext()) {
            ltam = (TsnAmtMx) Iter.next();
            lf_tmp = ltam.getYear_addup();
        }
        if (asShopBm.equalsIgnoreCase("all") ){
            ls_mc="";
        }else if (asShopBm.equalsIgnoreCase("yyb1") ){
            ls_mc="一部";
        }else if (asShopBm.equalsIgnoreCase("yyb2") ){
            ls_mc="二部";
        }else if (asShopBm.equalsIgnoreCase("yyb3") ){
            ls_mc="三部";
        }else if (asShopBm.equalsIgnoreCase("wgd") ){
            ls_mc="卫国道";
        }else if(asShopBm.equalsIgnoreCase("zll") ){
            ls_mc="竹林路";
        }else if(asShopBm.equalsIgnoreCase("yxl") ){
            ls_mc="越秀路";
        }else if(asShopBm.equalsIgnoreCase("bxl") ){
            ls_mc="本溪路";
        }else if(asShopBm.equalsIgnoreCase("cjl") ){
            ls_mc="澄江路";
        }else if(asShopBm.equalsIgnoreCase("gxs") ){
            ls_mc="干休所";
        }else {
            ls_mc=asShopBm+"店";
        }
        String ls_tmp="";
        ls_tmp+="<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">";
        ls_tmp+="<html xmlns=\"http://www.w3.org/1999/xhtml\">";
        ls_tmp+="<head>";
        ls_tmp+="<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />";
        ls_tmp+="<meta http-equiv=\"Refresh\" content=\"60\">";
        ls_tmp+="<META HTTP-EQUIV=\"pragma\" CONTENT=\"no-cache\">";
        ls_tmp+="<META HTTP-EQUIV=\"Cache-Control\" CONTENT=\"no-cache, must-revalidate\">";
        ls_tmp+="<META HTTP-EQUIV=\"expires\" CONTENT=\"Wed, 26 Feb 1997 08:21:57 GMT\">";
        ls_tmp+="<title></title>";
        ls_tmp+="<LINK href=\"http://168.42.8.40/todaysale/main.css\" type=text/css rel=stylesheet>";
        ls_tmp+="</head>";
        ls_tmp+="<body>";
        ls_tmp+="<DIV class=raised><B class=b1></B><B class=b2></B><B class=b3></B><B class=b4></B>";
        ls_tmp+="<DIV class=boxcontent>";
        ls_tmp+="<span class=\"STYLE1\">"+ls_mc+"销售总金额为 ￥：";
        ls_tmp+="<font size='6'>"+lf_tmp+"0</font></span>";
        ls_tmp+="<div id=\"img\" style=\"position:absolute; left: 350px; top: 10px; height: 54px; width: 380px;\" >";
        ls_tmp+="<span class=\"STYLE3\">※每5分钟自动刷新已上传销售数据，如无法查看本页说明当前网络连接出现故障!当前统计日期为："+adTsnDate+"</span> ";
        ls_tmp+="</div>";
        ls_tmp+="</div>";
        ls_tmp+="<B class=b4b></B><B class=b3b></B><B class=b2b></B><B class=b1b></B></DIV>";
        ls_tmp+="</body>";
        ls_tmp+="</html>";



        String realpath = "/datafiles/hzzs/todaysale/";
        //如果在windows上测试，请将realpath清空即可
        realpath = "";
        String outputFile = realpath + asShopBm  + ".html";
        try {
            FileOutputStream fOut = new FileOutputStream(outputFile);

            byte[] b = null;
            try {
                b = ls_tmp.getBytes("utf-8");
            } catch (UnsupportedEncodingException ex) {
                Logger.getLogger(DataProcessJdbcDao.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                fOut.write(b);
            } catch (IOException ex) {
                Logger.getLogger(DataProcessJdbcDao.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                fOut.flush();
            } catch (IOException ex) {
                Logger.getLogger(DataProcessJdbcDao.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                fOut.close();
            } catch (IOException ex) {
                Logger.getLogger(DataProcessJdbcDao.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (FileNotFoundException ex) {
            Logger.getLogger(DataProcessJdbcDao.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public List getShopList(Date adTsnDate,int aiBj) {
        List lTmp;
        List<ArchiDept> lTmp2;
        ArchiDept lad = new ArchiDept();
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        if(aiBj==0){
            sql="select id,bm,mc,pz,man_id,yybh,area from t_archi_dept where ckbj=1  order by id";
            lTmp=jt.query(sql,new RowMapperResultReader(new ArchiDeptRM()));
            
        }else{
            sql="select id,bm,mc,pz,man_id,yybh,area from t_archi_dept where id in (select tsn_id from t_bi_shoplist_noupload where tsn_date = ?)  order by id";
            Object[] params = new Object[]{adTsnDate};
            int[] types = new int[]{Types.DATE};
            lTmp=jt.query(sql,params,types,new RowMapperResultReader(new ArchiDeptRM()));
        }
        lTmp2 = new ArrayList<ArchiDept>();
        Iterator Iter = lTmp.iterator();
        while(Iter.hasNext()){
            lad=(ArchiDept)Iter.next();
            lTmp2.add(lad);
        }
        return lTmp2;
        
    }
    //定义供jdbctemplete使用的内部类
    class getFunctionRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index) throws SQLException {
            return rs.getString(1);
        }
    }

    class ArchiDeptRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            ArchiDept ad = new ArchiDept();
            ad.setId(new Integer(rs.getInt("id")));
            ad.setBm(rs.getString("bm"));
            ad.setMc(rs.getString("mc"));
            ad.setPz(new Integer(rs.getInt("pz")));
            ad.setMan_id(new Integer(rs.getInt("man_id")));
            ad.setYybh(new Integer(rs.getInt("yybh")));
            ad.setArea(new Float(rs.getFloat("area")));
            return ad;
        }
    }

    class TsnAmtMxYearRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            TsnAmtMx wavg = new TsnAmtMx();
            wavg.setYear_addup(new Float(rs.getFloat("year_addup")));
             return wavg;
        }
    }
    class TsnAmtMxWeekRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            TsnAmtMx wavg = new TsnAmtMx();
            wavg.setWeek_addup(new Float(rs.getFloat("week_addup")));
            wavg.setWeek_amt(new Integer(rs.getInt("week_amt")));
            return wavg;
        }
    }
    
}
